﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using MySql.Data.MySqlClient;
using WowSounds.Properties;
using System.Windows.Forms;

namespace WowSounds
{
    public class SoundEntry
    {
        private bool SelectText(String table)
        {
            TextTable = new DataTable();
            TextTable.Columns.Add("entry");
            TextTable.Columns.Add("content_default");
            TextTable.Columns.Add("content_loc8");
            TextTable.Columns.Add("sound");
            try
            {
                var connstr = String.Format("Server=localhost;Port=3306;Uid={0};Pwd={1};Connection Timeout=10", 
                    Settings.Default.User,
                    Settings.Default.Pass);

                MySqlConnection conn = new MySqlConnection(connstr);
                conn.Open();

                MySqlDataReader reader = new MySqlCommand(String.Format(
                        "SELECT `entry`, `content_default`, `content_loc8`, `sound` FROM {0} WHERE `sound` <> 0;", table), 
                    conn).ExecuteReader();


                //TextTable = reader.AsQueryabled();
                while (reader.Read())
                {
                    DataRow dr = TextTable.NewRow();
                    dr[0] = reader[0].ToString();
                    dr[1] = reader[1].ToString();
                    dr[2] = reader[2].ToString();
                    dr[3] = reader[3].ToString();
                    TextTable.Rows.Add(dr);
                }
                reader.Close();
                conn.Close();

                return true;
            }
            catch
            {
                MessageBox.Show("Can't connect to database!");
                return false;
            }
        }

        public static bool ExecuteQuery(String table, String text, String entry)
        {
            try
            {
                MySqlConnection conn = new MySqlConnection(String.Format(
                    "Server=localhost;Port=3306;Uid={0};Pwd={1};character set=utf8;Connection Timeout=10",
                    Settings.Default.User,
                    Settings.Default.Pass));

                conn.Open();
                var query = String.Format("UPDATE {0} SET `content_loc8` = '{1}' WHERE `entry` = '{2}';", table, text, entry);
                
                SqlLog(query);
                
                MySqlDataReader reader = new MySqlCommand(query, conn).ExecuteReader();

                reader.Close();
                conn.Close();

                return true;
            }
            catch 
            {
                MessageBox.Show("Can't connect to database!");
                return false; 
            }
        }

        private DataTable _SoundsTable = new DataTable();
        private DataTable SoundTable { get; set; }
        private DataTable TextTable { get; set; }

        public SoundEntry(ListView list, String table, String WowPatch, String Locale, bool local)
        {
            var pach = String.Format("{0}{1}\\locale-{1}.MPQ", WowPatch, Locale);
            if (!File.Exists(pach))
            {
                MessageBox.Show("File: "+pach + " is exists!");
                return;
            }
            var dbc = new MpqArchive(pach);
            var soundEntries = dbc.OpenFile(@"DBFilesClient\SoundEntries.dbc");
            new DBCReader(soundEntries, ref _SoundsTable, SoundEntryStructure);
            soundEntries.Close();
            dbc.Dispose();
            SoundTable = _SoundsTable;
            SelectText(table);

            LeftJoin(list, local);
        }

        private void LeftJoin(ListView list, bool loc)
        {
            list.Items.Clear();

            var qurey = from sounds in SoundTable.AsEnumerable()
                        join text in TextTable.AsEnumerable()
                        on sounds.Field<string>("ID") equals text.Field<string>("sound")
                        where (loc || text.Field<string>("content_loc8") == "")
                        select new 
                        {
                            ID = sounds.Field<string>("ID"),
                            FileName = String.Format("{0}\\{1}",
                                       sounds.Field<string>("Patch"),
                                       sounds.Field<string>("FileName1")),
                            Entry = text.Field<string>("entry"),
                            ContentDefault = text.Field<string>("content_default"),
                            ContentLoc = text.Field<string>("content_loc8")
                        };

            if (qurey == null)
                return;

            foreach (var str in qurey)
            {
                list.Items.Add(new ListViewItem(new string[] 
                {
                    str.ID,
                    str.Entry,
                    str.FileName,
                    str.ContentDefault,
                    str.ContentLoc
                }));
            }
        }

        public static void SqlLog(String query)
        {
            StreamWriter sw = new StreamWriter("sqllog.sql", true, Encoding.UTF8);
            sw.WriteLine(query);
            sw.Close();            
        }

        #region SoundEntry structure

        private readonly String[][] SoundEntryStructure = new String[][]
        {
            new String[] {"u",  "ID"},
            new String[] {"x",  ""},
            new String[] {"s",  "Folder"},
            new String[] {"s",  "FileName1"},
            new String[] {"s",  "FileName2"},
            new String[] {"s",  "FileName3"},
            new String[] {"s",  "FileName4"},
            new String[] {"s",  "FileName5"},
            new String[] {"s",  "FileName6"},
            new String[] {"s",  "FileName7"},
            new String[] {"s",  "FileName8"},
            new String[] {"s",  "FileName9"},
            new String[] {"s",  "FileName10"},
            new String[] {"x",  ""},
            new String[] {"x",  ""},
            new String[] {"x",  ""},
            new String[] {"x",  ""},
            new String[] {"x",  ""},
            new String[] {"x",  ""},
            new String[] {"x",  ""},
            new String[] {"x",  ""},
            new String[] {"x",  ""},
            new String[] {"x",  ""},
            new String[] {"s",  "Patch"},
            new String[] {"x",  ""}
        };

        #endregion

        private String Locales(String loc)
        {
            if (ContainString(loc, "ruRU"))
                return "content_loc8";
            else if (ContainString(loc, "ruRU"))
                return "content_loc8";
            else if (ContainString(loc, "deDE"))
                return "content_loc3";
            else if (ContainString(loc, "ruRU"))
                return "content_loc8";
            else if (ContainString(loc, "ruRU"))
                return "content_loc8";
            else if (ContainString(loc, "ruRU"))
                return "content_loc8";
            else if (ContainString(loc, "ruRU"))
                return "content_loc8";
            else
                return "content_default";
        }

        private bool ContainString(String input, String contain)
        {
            return input.IndexOf(contain) != -1;
        }
    }
}
